package com.tsfyun.scm.service.impl.file;

import cn.hutool.core.collection.CollUtil;
import com.tsfyun.common.base.dto.ExportExcelFileDTO;
import com.tsfyun.common.base.dto.ExportFileDTO;
import com.tsfyun.common.base.extension.ServiceImpl;
import com.tsfyun.common.base.util.DateUtils;
import com.tsfyun.common.base.util.StringUtils;
import com.tsfyun.common.base.util.TypeUtils;
import com.tsfyun.scm.dto.file.ExcelKeyValue;
import com.tsfyun.scm.dto.file.ExportExcelParamsDTO;
import com.tsfyun.scm.entity.file.ExportFile;
import com.tsfyun.scm.service.file.IExportFileService;
import com.tsfyun.scm.util.ExportUtil;
import jxl.Workbook;
import jxl.write.Number;
import jxl.write.*;
import org.apache.commons.collections4.ListUtils;
import org.apache.commons.collections4.map.LinkedMap;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.cloud.context.config.annotation.RefreshScope;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.io.File;
import java.lang.Boolean;
import java.math.BigDecimal;
import java.util.*;

/**
 * <p>
 *  服务实现类
 * </p>
 *
 *
 * @since 2020-03-04
 */
@RefreshScope
@Service
public class ExportFileServiceImpl extends ServiceImpl<ExportFile> implements IExportFileService {

    @Value(value = "${file.directory}")
    private String fileDirectory;

    @Override
    public Long exportExcel(ExportExcelFileDTO dto) throws Exception {
        String path = fileDirectory+"temp/excel/";
        String sysFileName = new Date().getTime()+"-"+ StringUtils.randomStr6()+".xls";
        String fileName = TypeUtils.castToString(dto.getFileName(),new Date().getTime()+"").concat(".xls");
        new File(path).mkdirs();
        WritableWorkbook book = Workbook.createWorkbook(new File(path+sysFileName));
        WritableSheet sheet = book.createSheet(TypeUtils.castToString(dto.getSheetName(),"sheet"),0);
        WritableFont fontTitle = new WritableFont(WritableFont.TIMES,11,WritableFont.BOLD);
        WritableCellFormat formatTitle = new WritableCellFormat(fontTitle); //表头字体
        formatTitle.setAlignment(jxl.format.Alignment.CENTRE);//设置为居中
        formatTitle.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.NONE);//设置无边框
        WritableFont fontContent = new WritableFont(WritableFont.createFont("楷体_GB2312"),10,WritableFont.NO_BOLD);
        WritableCellFormat formatContent = new WritableCellFormat(fontContent);
        formatContent.setAlignment(jxl.format.Alignment.CENTRE);
        formatContent.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.NONE);
        formatContent.setLocked(false);
        WritableFont fontLocked = new WritableFont(WritableFont.createFont("楷体_GB2312"),10,WritableFont.NO_BOLD);
        WritableCellFormat formatLocked = new WritableCellFormat(fontLocked);
        formatLocked.setAlignment(jxl.format.Alignment.CENTRE);
        formatLocked.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.NONE);
        formatLocked.setWrap(true);
        Label label;
        Integer row = 0;
        Boolean showIndex = dto.getShowIndex();//是否显示序号列
        if(CollUtil.isNotEmpty(dto.getTitles())){
            if(Objects.equals(showIndex,Boolean.TRUE)) {
                label = new Label(0, row, "序号", formatTitle);
                sheet.addCell(label);
            }
            for(int k =0;k<dto.getTitles().size();k++){
                String title = dto.getTitles().get(k);
                if(Objects.equals(showIndex,Boolean.TRUE)) {
                    label = new Label(k + 1, row, title, formatTitle);
                } else {
                    label = new Label(k, row, title, formatTitle);
                }
                sheet.addCell(label);
            }
            row++;
        }
        if(CollUtil.isNotEmpty(dto.getDatas())){
            List<Map<String,Object>> resultList = (List<Map<String,Object>>)dto.getDatas();
            Number number;
            Map<String, BigDecimal> totalMap = new LinkedMap<>();
            Map<Integer, String> totalIdxMap = new LinkedMap<>();
            List<String> totalList = ListUtils.emptyIfNull(dto.getTotal());
            for (int i = 0; i < resultList.size(); i++) {
                Map<String,Object> resultMap = resultList.get(i);
                if(Objects.equals(showIndex,Boolean.TRUE)) {
                    number = new Number(0,row,row,formatLocked);//序号列值
                    sheet.addCell(number);
                }
                int j = 0;
                for(Map.Entry<String,Object> entry : resultMap.entrySet()) {
                    WritableCellFormat cellFormat = formatContent;
                    if(StringUtils.isNotEmpty(entry.getValue())){
                        if(entry.getValue() instanceof java.lang.Number){
                            if(Objects.equals(showIndex,Boolean.TRUE)) {
                                number = new Number(j + 1, i + 1, Double.parseDouble(entry.getValue().toString()), cellFormat);
                            } else {
                                number = new Number(j, i + 1, Double.parseDouble(entry.getValue().toString()), cellFormat);
                            }
                            sheet.addCell(number);
                            //统计求和
                            if(totalList.contains(entry.getKey())){
                                BigDecimal total = Optional.ofNullable(totalMap.get(entry.getKey())).orElse(BigDecimal.ZERO).add(new BigDecimal(entry.getValue().toString()));
                                totalMap.put(entry.getKey(),total);
                                totalIdxMap.put(j,entry.getKey());
                            }
                        }else{
                            if(Objects.equals(showIndex,Boolean.TRUE)) {
                                label = new Label(j + 1, row, entry.getValue().toString(), cellFormat);
                            } else {
                                label = new Label(j, row, entry.getValue().toString(), cellFormat);
                            }
                            sheet.addCell(label);
                        }
                    }else{
                        if(Objects.equals(showIndex,Boolean.TRUE)) {
                            label = new Label(j + 1, row, "", formatContent);
                        } else {
                            label = new Label(j, row, "", formatContent);
                        }
                        sheet.addCell(label);
                    }
                    j++;
                }
                row++;
            }
            // 合计数据
            if(CollUtil.isNotEmpty(totalIdxMap)){
                int j = 0;
                label = new Label(0, row, "合计:", formatTitle);
                sheet.addCell(label);
                for(Map.Entry<String,Object> entry : resultList.get(0).entrySet()) {
                    String key = totalIdxMap.get(j);
                    if(StringUtils.isNotEmpty(key)&& Objects.nonNull(totalMap.get(key))){
                        if(Objects.equals(showIndex,Boolean.TRUE)) {
                            sheet.addCell(new Number(j + 1, row, totalMap.get(key).doubleValue(), formatTitle));
                        } else {
                            sheet.addCell(new Number(j, row, totalMap.get(key).doubleValue(), formatTitle));
                        }
                    }else{
                        if(Objects.equals(showIndex,Boolean.TRUE)) {
                            sheet.addCell(new Label(j + 1, row, "", formatTitle));
                        } else {
                            sheet.addCell(new Label(j, row, "", formatTitle));
                        }
                    }
                    j++;
                }
                row++;
            }
        }
        ExportUtil.changeSheetWidth(sheet);
        book.write();
        book.close();

        ExportFile exportFile = new ExportFile();
        exportFile.setPath(path+sysFileName);
        exportFile.setFileName(fileName);
        exportFile.setOnce(Boolean.TRUE);
        exportFile.setOperator(TypeUtils.castToString(dto.getOperator(),"-"));
        super.saveNonNull(exportFile);
        return exportFile.getId();

    }

    @Override
    public Long exportExcel(ExportExcelParamsDTO dto)  throws Exception{
        String path = fileDirectory+"temp/excel/";
        String sysFileName = new Date().getTime()+"-"+ StringUtils.randomStr6()+".xls";
        String fileName = TypeUtils.castToString(dto.getFileName(),new Date().getTime()+"").concat(".xls");
        new File(path).mkdirs();
        WritableWorkbook book = Workbook.createWorkbook(new File(path+sysFileName));
        WritableSheet sheet = book.createSheet(TypeUtils.castToString(dto.getSheetName(),"sheet"),0);
        WritableFont fontTitle = new WritableFont(WritableFont.TIMES,11,WritableFont.BOLD);
        WritableCellFormat formatTitle = new WritableCellFormat(fontTitle); //表头字体
        formatTitle.setAlignment(jxl.format.Alignment.CENTRE);//设置为居中
        formatTitle.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.NONE);//设置无边框
        WritableFont fontContent = new WritableFont(WritableFont.createFont("楷体_GB2312"),10,WritableFont.NO_BOLD);
        WritableCellFormat formatContent = new WritableCellFormat(fontContent);
        formatContent.setAlignment(jxl.format.Alignment.CENTRE);
        formatContent.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.NONE);
        formatContent.setLocked(false);
        WritableFont fontLocked = new WritableFont(WritableFont.createFont("楷体_GB2312"),10,WritableFont.NO_BOLD);
        WritableCellFormat formatLocked = new WritableCellFormat(fontLocked);
        formatLocked.setAlignment(jxl.format.Alignment.CENTRE);
        formatLocked.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.NONE);
        formatLocked.setWrap(true);
        Label label = null;
        Integer row = 0;
        Boolean showIndex = dto.getShowIndex();//是否显示序号列
        if(CollUtil.isNotEmpty(dto.getFiles())){
            if(Objects.equals(showIndex,Boolean.TRUE)) {
                label = new Label(0, row, "序号", formatTitle);
                sheet.addCell(label);
            }
            for(int k =0;k<dto.getFiles().size();k++){
                String title =  dto.getFiles().get(k).getValue();
                if(Objects.equals(showIndex,Boolean.TRUE)) {
                    label = new Label(k + 1, row, title, formatTitle);
                } else {
                    label = new Label(k, row, title, formatTitle);
                }
                sheet.addCell(label);
            }
            row++;

            List<Map<String,Object>> resultList = beanMapper.mapAsList(dto.getDatas(),Map.class);
            Number number;
            List<String> totalList = ListUtils.emptyIfNull(dto.getTotal());
            Map<String, BigDecimal> totalMap = new LinkedMap<>();
            for (int i=0;i<resultList.size();i++){
                if(Objects.equals(showIndex,Boolean.TRUE)) {
                    number = new Number(0,row,row,formatLocked);//序号列值
                    sheet.addCell(number);
                }
                Map<String,Object> resultMap = resultList.get(i);

                for(int k =0;k<dto.getFiles().size();k++){
                    ExcelKeyValue excelKeyValue = dto.getFiles().get(k);
                    Object value = resultMap.get(excelKeyValue.getKey());
                    if(Objects.nonNull(value)){
                        if(value instanceof java.lang.Number){// 数值型
                            if(Objects.equals(showIndex,Boolean.TRUE)) {
                                number = new Number(k + 1, row, Double.parseDouble(value.toString()), formatContent);
                            } else {
                                number = new Number(k, row, Double.parseDouble(value.toString()), formatContent);
                            }
                            sheet.addCell(number);
                            //统计求和
                            if(totalList.contains(excelKeyValue.getKey())){
                                BigDecimal total = Optional.ofNullable(totalMap.get(excelKeyValue.getKey())).orElse(BigDecimal.ZERO).add(new BigDecimal(value.toString()));
                                totalMap.put(excelKeyValue.getKey(),total);
                            }
                        }else if(value instanceof java.util.Date){// 日期
                            if(Objects.equals(showIndex,Boolean.TRUE)) {
                                label = new Label(k + 1, row, DateUtils.formatShort((Date)value), formatContent);
                            } else {
                                label = new Label(k, row, DateUtils.formatShort((Date)value), formatContent);
                            }
                            sheet.addCell(label);
                        }else{
                            if(Objects.equals(showIndex,Boolean.TRUE)) {
                                label = new Label(k + 1, row, value.toString(), formatContent);
                            } else {
                                label = new Label(k, row, value.toString(), formatContent);
                            }
                            sheet.addCell(label);
                        }
                    }else{
                        if(Objects.equals(showIndex,Boolean.TRUE)) {
                            label = new Label(k + 1, row, "", formatContent);
                        } else {
                            label = new Label(k, row, "", formatContent);
                        }
                        sheet.addCell(label);
                    }
                }
                row++;
            }
            // 合计数据
            if(CollUtil.isNotEmpty(totalMap)){
                label = new Label(0, row, "合计:", formatTitle);
                sheet.addCell(label);
                for(int k =0;k<dto.getFiles().size();k++){
                    BigDecimal total = totalMap.get(dto.getFiles().get(k).getKey());
                    if(Objects.nonNull(total)){
                        if(Objects.equals(showIndex,Boolean.TRUE)) {
                            sheet.addCell(new Number(k + 1, row, total.doubleValue(), formatTitle));
                        } else {
                            sheet.addCell(new Number(k, row, total.doubleValue(), formatTitle));
                        }
                    }else{
                        if(Objects.equals(showIndex,Boolean.TRUE)) {
                            sheet.addCell(new Label(k + 1, row, "", formatTitle));
                        } else {
                            sheet.addCell(new Label(k, row, "", formatTitle));
                        }
                    }
                }
                row++;
            }
        }


        ExportUtil.changeSheetWidth(sheet);
        book.write();
        book.close();

        ExportFile exportFile = new ExportFile();
        exportFile.setPath(path+sysFileName);
        exportFile.setFileName(fileName);
        exportFile.setOnce(Boolean.TRUE);
        exportFile.setOperator(TypeUtils.castToString(dto.getOperator(),"-"));
        super.saveNonNull(exportFile);
        return exportFile.getId();
    }

    @Override
    public File findExportFile(Long id) {
        ExportFile exportFile = super.getById(id);
        if(exportFile!=null){
            return new File(exportFile.getPath());
        }
        return null;

    }

    @Override
    public void removeExportFile(Long id, String filePath) {
        super.removeById(id);
        File file = new File(filePath);
        if(file.exists()) {
            file.delete();
        }
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public Long save(ExportFileDTO dto) {
        ExportFile exportFile = beanMapper.map(dto,ExportFile.class);
        super.saveNonNull(exportFile);
        return exportFile.getId();
    }
}
